import pandas as pd
import os
import sys
import numpy as np
from openpyxl import load_workbook

def check_dependencies():
    """检查必要的依赖是否已安装"""
    try:
        import xlrd
        import openpyxl
        return True
    except ImportError as e:
        print("缺少必要的依赖库，请运行以下命令安装：")
        print("pip install xlrd>=2.0.1 openpyxl")
        return False

def get_merged_cells_info(excel_file, sheet_name=0):
    """获取合并单元格信息"""
    if excel_file.endswith('.xlsx'):
        wb = load_workbook(excel_file)
        ws = wb[sheet_name] if isinstance(sheet_name, str) else wb.worksheets[sheet_name]
        merged_cells = []
        for merged_range in ws.merged_cells.ranges:
            min_col, min_row, max_col, max_row = merged_range.bounds
            merged_cells.append({
                'start_row': min_row - 1,  # 转换为0基索引
                'end_row': max_row - 1,
                'start_col': min_col - 1,
                'end_col': max_col - 1
            })
        return merged_cells
    else:
        # 对于.xls文件，使用pandas读取
        df = pd.read_excel(excel_file, sheet_name=sheet_name)
        return []  # xlrd不支持获取合并单元格信息

def excel_to_html(excel_file, output_html=None, sheet_name=0):
    """
    将Excel文件转换为HTML表格，支持复杂的合并单元格
    
    参数:
        excel_file (str): Excel文件路径
        output_html (str, optional): 输出HTML文件路径，如果不指定则使用Excel文件名
        sheet_name (str/int, optional): 工作表名称或索引，默认为第一个工作表
    
    返回:
        str: 生成的HTML内容
    """
    try:
        # 检查文件是否存在
        if not os.path.exists(excel_file):
            raise FileNotFoundError(f"找不到文件: {excel_file}")
            
        # 检查文件格式
        file_ext = os.path.splitext(excel_file)[1].lower()
        if file_ext not in ['.xls', '.xlsx']:
            raise ValueError(f"不支持的文件格式: {file_ext}，请使用.xls或.xlsx格式")
        
        # 获取合并单元格信息
        merged_cells = get_merged_cells_info(excel_file, sheet_name)
        
        # 读取Excel文件，保持表头结构
        print(f"正在读取文件: {excel_file}")
        df = pd.read_excel(
            excel_file,
            sheet_name=sheet_name,
            header=None  # 不使用默认的表头处理
        )
        
        # 处理合并单元格
        table_html = '<table class="table table-striped table-bordered">\n'
        
        # 添加表头
        table_html += '<thead>\n'
        for i in range(2):  # 处理两行表头
            table_html += '<tr>\n'
            for j in range(len(df.columns)):
                cell_value = str(df.iloc[i, j])
                if cell_value == 'nan' or cell_value == 'NaN':
                    cell_value = ''
                    
                # 检查是否是合并单元格
                is_merged = False
                rowspan = 1
                colspan = 1
                for merged_cell in merged_cells:
                    if (merged_cell['start_row'] <= i <= merged_cell['end_row'] and 
                        merged_cell['start_col'] <= j <= merged_cell['end_col']):
                        if i == merged_cell['start_row'] and j == merged_cell['start_col']:
                            rowspan = merged_cell['end_row'] - merged_cell['start_row'] + 1
                            colspan = merged_cell['end_col'] - merged_cell['start_col'] + 1
                            is_merged = True
                        else:
                            continue
                
                if is_merged:
                    table_html += f'<th rowspan="{rowspan}" colspan="{colspan}">{cell_value}</th>\n'
                else:
                    if i == merged_cell['start_row'] and j == merged_cell['start_col']:
                        table_html += f'<th>{cell_value}</th>\n'
            table_html += '</tr>\n'
        table_html += '</thead>\n'
        
        # 添加数据行
        table_html += '<tbody>\n'
        for i in range(2, len(df)):
            table_html += '<tr>\n'
            for j in range(len(df.columns)):
                cell_value = str(df.iloc[i, j])
                if cell_value == 'nan' or cell_value == 'NaN':
                    cell_value = ''
                table_html += f'<td>{cell_value}</td>\n'
            table_html += '</tr>\n'
        table_html += '</tbody>\n'
        table_html += '</table>'
        
        # 添加Bootstrap样式
        html_template = f"""
        <!DOCTYPE html>
        <html>
        <head>
            <meta charset="UTF-8">
            <title>{os.path.basename(excel_file)}</title>
            <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
            <style>
                .table {{
                    width: 100%;
                    margin-bottom: 1rem;
                    background-color: transparent;
                    border-collapse: collapse;
                }}
                .table th,
                .table td {{
                    padding: 0.75rem;
                    vertical-align: middle;
                    border: 1px solid #dee2e6;
                    text-align: center;
                }}
                .table thead th {{
                    vertical-align: middle;
                    border-bottom: 2px solid #dee2e6;
                    background-color: #f8f9fa;
                    white-space: nowrap;
                    font-weight: bold;
                }}
                .table tbody + tbody {{
                    border-top: 2px solid #dee2e6;
                }}
                .table-responsive {{
                    overflow-x: auto;
                    -webkit-overflow-scrolling: touch;
                }}
                .table td:not(:first-child) {{
                    text-align: right;
                }}
                .table th {{
                    word-wrap: break-word;
                    min-width: 80px;
                }}
                /* 统计时间样式 */
                .report-time {{
                    text-align: center;
                    margin-bottom: 20px;
                    font-weight: bold;
                }}
            </style>
        </head>
        <body>
            <div class="container-fluid mt-4">
                <div class="report-time">统计时间段：2024年1月1日至2024年12月31日</div>
                <div class="table-responsive">
                    {table_html}
                </div>
            </div>
            <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
        </body>
        </html>
        """
        
        # 如果指定了输出文件，则保存HTML
        if output_html:
            with open(output_html, 'w', encoding='utf-8') as f:
                f.write(html_template)
            print(f"HTML文件已保存到: {output_html}")
        
        return html_template
        
    except Exception as e:
        print(f"转换过程中发生错误: {str(e)}")
        raise

def main():
    # 检查依赖
    if not check_dependencies():
        sys.exit(1)
        
    # 使用示例
    excel_file = "./rr_excel.xls"  # 替换为您的Excel文件路径
    
    try:
        # 生成HTML
        html_content = excel_to_html(
            excel_file,
            output_html="rr_excel_out.html",  # 输出文件路径
            sheet_name=0  # 第一个工作表
        )
        
        print("转换成功！")
        
    except Exception as e:
        print(f"程序执行过程中发生错误: {str(e)}")
        sys.exit(1)

if __name__ == "__main__":
    main() 